Amazon Athena Overview

Amazon Athena is an interactive query service that makes it easy to analyze data directly in Amazon S3 using standard SQL.
Use cases : Buisness intelligence / analytics / reporting, analyze, & query VPC Flow logs, ELB logs, CloudTrails, etc.

- Serverless query service to analyse data stored in Amazon S3.
- Use standard SQL language to query files(built on pesto).
- Support CSV, JSON, ORC, Avro and Parquet.
- Pricing $5.00 per TB of data scanned.
- Commonly used with Amazon Quicksight for reporting/dashboards.

Amazon Athena : Performance Improvement

- Use Columnar Data for cost savings (by doing less scan).

- Apache Parquet or ORC is recommended.
- Huge performance improvement.
- Use Glue to convert your data to Parquet or ORC format.

- Compress data for smaller retrievals (bzip2, gzip, Iz4, snappy, zlip...)
- Partition datasets in S3 for easy querying on virtual columns

- s://yourbucketname/pathtotable

/[PARTITION_COLUMN_NAME]=[VALUE]
/[PARTITION_COLUMN_NAME]=[VALUE]
etc..

- Example s3://athenabucket/flight/parquet/year=1999/month=1/day=1/

- Use larger files (>128MB) to minimize overhead.

Amazon Athena : Federated Query

- Allows you to run SQL queries across data stored in relation, non-relational, objects or custom data sources (AWS or on-premises).

- Uses Data Sources Connectors that run on AWS Lambda to run federated queries (like on Cloudwatch logs, DynamoDB, RDS DB, Elasticache etc..)

- Store results back in Amazon S3.

Amazon DocumentDB Overview

We will talk about DocumentDB so we all know about Aurora RDS and Aurora RDS is AWS implementation of Postgresql or MySQL because it has the same API but it's built on AWS and it has some nice benefits to be cloud native.
DocumentDB is the same as Aurora RDS but for MongoDB (Which is NoSQL database).

- Mongodb is used to store, query and index JSON data so thanks to DocumentDB.
- Similar "deplyement concept" as Aurora RDS.
- Fully Managed, highly available with replication across 3 AZ.
- DocumentDB storage automatically grows in increments of 10GB.
- Automatically scales to workloads with millions of requests per seconds.

If using MongoDB on AWS so you pay for what you use in DocumentDB and there is no upfront cost but you need to understand the Architecture of DocumentDB, which is very similar to Aurora in order to understand its pricing.

DocumentDB - Pricing

- Pay as you Go, so pay for what you use. No up front cost.
- On-Demand Instances (pay per second with the minimum of 10 minutes)
- Database I/O: amount of IO used when Read/Write (per million IOs)
- Database Storage (per GB/month)
- Backup Storage (per GB/month)

We have database storage and then we have On Demand instances. We have primary instance and then replica instances that's up to you're going to pay for these on-demand instances, you can pay per second with the minimum of 10 minutes then these instances are going to do a read and write operations against your database storage so you're going to base for database IOs and this going to be counted per million IOs then of course the data is being stored on your database storage and therefore you're going to pay for the derby storage itself in gigabytes per month.

Amazon Timestream Overview

As name indicates, Amazon Timestream is a time series database so it's fully managed, fast, scalable, serverless time series database. Use cases for timestream would be to have an IoT application, operational applications, real-time Analytics or everything related to a Time series database.

- Automatically scale up/down to scale capacity.
- Store and analyse trillions of events per day.
- 1000s times faster & 1/10th the cost of relation databases.
- Schedule queries, multi-measure records and SQL Compatibility.
- Data storage tiering : Recent data kept in memory and historical data kept in a cost-optimised storage.
- Built-in time series analytics functions (helps you identify patterns in your data in real-time.
- Supports Encryption in-transit and at rest.

Amazon Timestream Architecture

Timeseries database can receive data from AWS IoT so internet of things, the Kineses Data Streams through them that can receive data as well, Prometheus, Telegraf, their integrations for that case the streams as well through conditions data analytics for Apache link can receive data into Amazon time stream and Amazon msk as well through the same process.

In terms of what can connect to do timesstream where can build a dashboards using Amazon Quicksight and machine learning using Amazon SageMaker, can connect to grafana or because there is a standard jdbc connection into your database and the application that is compatible with jdbc and SQL can leverage Amazon timestream.

Amazon Redshift Overview

Amazon Redshift is a data warehouse product which forms part of the larger cloud-computing platform Amazon Web Services.

- Redshift is based on PostgreSQL, but it's not used for OLTP. 
- It's OLAP - Online Analytical Processing (analysis and data warehousing).
- Built on top of technology from the massive parallel processing data warehouse company ParAccel, to handle large scale data sets and database migrations.
- 10X better performance than other data warehouses, Scale to PBs of data.
- Columnar storage of data (instead of row based data).
- Pay as you go based on the instances provisioned.
- Can use SQL interface for performing the queries.
- BI tools such as AWS QuickSight or Tableau integrate with it.
- How to load data into Redshift?
Data is loaded from Amazon S3(using COPY command), Kinesis Firehose (To load data near real-time), DynamoDB, DMS (Data Migration Service)...
- Based on node type : up to 100+ nodes, each node can have up to 16 TB of storage space.
- Can provision multiple nodes, with Multi-AZ only for some clusters.
- There are two types of nodes in Redshit :

- Leader Node : For query planning and result aggregation from compute node.
- Compute Node : For performing the queries, and send result to leader node.

- As Redshit is a managed service; so we only get Backup & Restore, Security VPC enhancements /IAM (for accessing cluster)/KMS (for encryption), Monitoring using Cloudwatch.
- Redshift Enhanced VPC routing: COPY / UNLOAD goes through VPC (for better performance and lower cost).
- Redshift is provisioned, so it's worth it when you have a sustained usage (use Athena if the queries are sporadic instead).

Redshift - Snapshot & DR

- Snapshots are point-in-time backups of your cluster, stored internally in S3.
- Snapshots are incremental (Only what has changed in your redshift cluster will be saved).
- You can restore a snapshot into a new cluster (You've to create new cluster to restore the data).
- There are two types of snapshots :

- Automated Snapshot : Happens every 8 hours or every 5 GB of data change in the cluster or on a schedule you set. And set retention ( for example, like for 30 Days and after 30 days snapshot will be automatically deleted).
- Manual Snapshot : On-demand snapshot, retained until you delete it.

It is very similar, how RDS works. But one cool thing in Redshift, you can configure Amazon Redshift to automatically copy snapshots(automated or manual) of a cluster to another AWS Region and this is very useful to setup disaster recovery for your Redshift cluster.

Cross-Region Snapshot Copy for an KMS-Encrypted Redshift Cluster

How to copy a cross-regions snapshot for kms encrypted redshift snapshots?

You have the source and your snapshot is in your source region it's encrypted using the KMS Key A, and you want to copy it into destination came as KMS key B so what we need to do is call a redshift "snapshot copy grant" and that it will allow redshift the service to perform encryption operations in the destination region when this is done then you can copy your snapshot from the original one into the region 2 and then it will be encrypted by redshift with the correct kms key so the the really magical thing here that happens is that you need to have and create a snapshot to copy.

Redshift Spectrum

- Query data that is already in S3 without loading it. - Must have a Redshift cluster available to start the query. - The query is then submitted to thousand of Redshift Specturm nodes.

How does that work?

Here, our existing redshift cluster has a leader node and has a bunch of compute nodes then if we do a query on data on Amazon S3 that will look like above image then what's going to happen is that redshift is going to spin up a lot of virtual spectrum nodes they will do the computation and the query over the data set in Amazon S3 and then once the result is there is going to be sent back to the compute nose for aggregation and then will be rolled back into the leader node.

Redshift WorkLoad Management (WLM)

- It enables you to flexibly manage queries priorities within workloads.
- Use case for this is to prevent short files from inquiries from getting stuck behind long running queries.
- You can define multiple query queues you get a very simple super user queue and user defined queue and so on and then,
- Route your query to the proper queue at runtime.

Lets take an example, here in Amazon redshift and have three queues. One is the Super User Queue, the short running queue and the long running queue the names are pretty explicits and say we have two kinds of users we have Admin and User.

Now, they want their queries to be done as soon as possible right so they will do system queries and then these queries will go directly into the Super User Queue.
And this will have priority the user may have some short-running queries and these we want to have into their short-running query queue this way we are sure that it will just be a lot of short-running queries.
But if the user is submitting a longer running query and we're not gonna take a lot of time then we should send it directly into the long running queue to make sure not to block the short running queries.
So we have two kind of work load management :
- Automatic WLM : where the cues and resources are managed by shift.
- Manual WLM : which accusing resources are managed by you (i.e. the user).

Redshift Concurrency Scaling Cluster

- It enables you to provide closely fast performance with virtually and limited number of users and queries.
- Redshift automatically adds additional cluster capacity on the fly so this additional concert capacity is called Concurrencies Scaling Cluster and allows you to process an increase in request.
- Ability to decide which queries sent to the Concurrencies Scaling Cluster using WLM.

So let's say in the below image, we have a normal version cluster with a few nodes under and then this sustains you know a bunch of users that we have normally let's say tomorrow we get a lot more users submitting queries into Amazon redshift then automatically the concurrency scaling cluster will add those automatically to accommodate for these number of users.

Now we get the ability to decide which queries get sent to the concurrency is getting cluster using the WLM feature we just saw in previous discussion and this feature is charged per second.

Choosing the Right Cloud Database Service offered by cloud providers : AWS, Azure and GCP

The majority of cloud providers started off treating databases as application that could be run on general-purpose compute instances. However, they quickly started adding higher-level application services to their IaaS offerings. Cloud databases have grown to be a crucial area of technological advancement, with manufacturers battling for customers by introducing several database kinds.

Architects, administrators, and developers must be aware of the various database options available because the IT industry is currently experiencing a significant displacement of data-centric workloads to the cloud.

Ensure that the IT team (including DBA and App Team) choose the cloud database provider that best suits the requirements of your business by following the below tips :

1. Become familiar with the database's options:

The deployment architecture and the database requirements are the two main factors to take into account while selecting cloud database services. Each element will include parameters specific to a given application.

The following are the variables that influence cloud database selection:
- Deployment environment: on-site, in a public cloud or clouds, or a combination of hosted and private resources.
- Database manager: specifically, whether the database will be self-managed or a cloud service (DBaaS).
- Database type: including relational SQL (RDBS), distributed SQL, NoSQL variants like a column (key-value) store, wide column or document DB, cache, graph, or various specialty databases like time series and quantum ledger.
- Availability and resilience requirements: these will dictate whether many replicated and synchronized instances are required.
- Performance and capacity requirements: these include transactions per second, query latency, and scalability of both database throughput and capacity.
- Flexibility: defining precise version criteria and the degree of authority required for an IT staff to manage more intricate elements like database setup and configuration.

IT teams including DBA's and Application team should investigate the spectrum of database services offered by AWS, Microsoft Azure, and Google Cloud Platform to suit their individual requirements.

2. Examine cloud trade-offs:

Cloud database services are more cost-effective for businesses looking to cut costs than self-managed internal database systems because they do not require hardware, system administration, or any DBA overhead. Using cloud goods built on open source or in-house produced software, which does not require license costs, also increases the savings for businesses.

Cloud database services work well for businesses that:
- Desire the simplest, fastest implementation possible.
- Desire access to a large selection of database products without having to set up and maintain each one.
- Reluctant to oversee the software.
- Require maximum expandability.
- Desire high availability through a number of widely separated instances.
- Recognize the benefits of the cloud's intrinsic security and its shared security paradigm; additionally, you don't have to micromanage every database option.

In general, cloud users need to understand the limitations on the capacity and performance of specific database instances. Nonetheless, problems may frequently be handled using a distributed architecture that takes advantage of networking and replication capabilities included in cloud solutions.

3. Analyze your consumption and business:

Consider some of the following aspects while making the best choice for the workloads at your organisation.
First, database and cloud infrastructure products are better suited for new applications than for aging ones. IT departments may decide to shift old systems to the cloud, meanwhile, if they wish to get off outdated hardware, experience utilization spikes, and anticipate cost savings from the cloud's consumption-based pricing. IT teams may need to relocate a historical database in order to connect it with more recent apps if an organization is pursuing a cloud-native strategy.

However, a hybrid cloud deployment—where on-premises data is replicated to or accessed from the cloud—is typically a better starting point and provides better risk management than a lift-and-shift database migration if you're hesitant to jump right into the cloud. By using a hybrid strategy, IT can also move its primary system from the on-premises database to the cloud, with the on-premises database serving as a backup.

Systems architects and application developers need to be especially mindful of service costs when designing cloud-based environments, regardless of the cloud database they use. This is because careless usage and setups can quickly negate any potential savings from cloud deployments.

RDS SQL Server : Enable and Configure Database mail profile

In this article will discuss about enabling and configuring Database Mail on AWS RDS SQL Server.

Before configuring Database Mail, you've to first enable it through a database parameter group.
In Amazon RDS, parameter groups act as a container for engine configuration values that are applied to one or more DB instances. Each RDS instance comes with an associated default parameter group; however, we can’t modify it.

You can either use a new parameter group or an existing created parameter group. If choosing an existing parameter group, it must support your SQL Server instance edition and version.
To enable Database Mail through a parameter group, complete the following steps:

- On the Amazon RDS console, choose Parameter groups.
- Choose the parameter group you want to use.
- In the search box, enter database mail xps.
- Choose Edit Parameters to modify the value.
- For Values, choose 1.
- Save your changes.
- On the Amazon RDS console, choose Databases.
- Choose the instance you want to use.
- Choose Modify.
- Under Database options, choose the parameter group that has database mail xps set to 1.
- Choose Continue.
- Under Scheduling of modifications, choose Immediately.
- Choose Modify DB Instance to apply the changes.

Before we can use Database Mail, we need to set up a mail configuration.
- Launch SQL Server Management Studio.
- Connect to the SQL Server engine of the RDS instance that Database Mail is enabled for.
- Open a new query.
Use the following stored procedures to create a simple Database Mail configuration.

- Create a Database Mail profile (a profile is a container used to store email accounts). See the following code:
use msdb
go

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Notifications',
@description = 'Profile used for sending outgoing notifications using SES.' ;

- Add principles to the profile; use public so any user can access the profile:
use msdb
go

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Notifications',
@principal_name = 'public',
@is_default = 1 ;

We can grant the permissions on Database Mail objects as needed, but public is fine for now.
- Create the Database Mail account (make sure to enter the correct SMTP credentials):

use msdb
go

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Acc1',
@description = 'Mail account for sending outgoing notifications.',
@email_address = 'example@example.com',
@display_name = 'Automated Mailer',
@mailserver_name = 'email-smtp.us-west-2.amazonaws.com',
@port = 587,
@enable_ssl = 1, @username = 'SMTP-username',
@password = 'SMTP-password' ;

- Add Database Mail account to the Database Mail profile:
use msdb
go

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Notifications',
@account_name = 'Acc1',
@sequence_number = 1 ;

- Sending a test email
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Notifications',
@recipients = 'success@simulator.amazonses.com',
@body = 'The database mail configuration was completed successfully.',
@subject = 'Automated Success Message';
GO

Then run this stored procedure to see all email items:
SELECT * FROM msdb.dbo.sysmail_allitems

Please refer the below document for more details on the same : [+] Using Database Mail on Amazon RDS for SQL Server; https://aws.amazon.com/blogs/database/using-database-mail-on-amazon-rds-for-sql-server/

RDS SQL Server : Script to automate native backup of all the individual databases using msdb.dbo.rds_backup_database Stored procedure

You can use the below command to automate native backup of all the databases using msdb.dbo.rds_backup_database Stored procedure. You can also use the below script to schedule as a agent job to take native backup (in .bak file) of your all databases.

Script to take Full backup without encryption

DECLARE @BackupFileName varchar(50)
DECLARE @DBName sysname
DECLARE @S3ARN_Prefix nvarchar(100)
DECLARE @S3ARN nvarchar(100)
SET @S3ARN_Prefix = 'arn:aws:s3:::awsbucket_name/' -- update your bucketname

DECLARE DBBackup CURSOR FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('rdsadmin', 'master', 'model', 'msdb', 'tempdb')
AND state_desc = 'ONLINE'
AND user_access_desc = 'MULTI_USER'
AND is_read_only = 0

OPEN DBBackup
FETCH NEXT FROM DBBackup INTO @DBName
WHILE (@@FETCH_STATUS = 0)

BEGIN
SET @BackupFileName = @DBName + '_' + REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(),20),'-',''),':',''),' ','') + '.bak'
SET @S3ARN = @S3ARN_Prefix + @BackupFileName
EXEC msdb.dbo.rds_backup_database
@source_db_name = @DBName,
@S3_arn_to_backup_to = @S3ARN,
@overwrite_S3_backup_file = 0
FETCH NEXT FROM DBBackup INTO @DBName
END

Script to take Full backup with encryption

DECLARE @BackupFileName varchar(50)
DECLARE @DBName sysname
DECLARE @S3ARN_Prefix nvarchar(100)
DECLARE @S3ARN nvarchar(100)
DECLARE @KMS_master_key_ARN nvarchar(100)
SET @S3ARN_Prefix = 'arn:aws:s3:::awsbucket_name/' -- update your bucketname
SET @KMS_master_key_ARN = 'arn:aws:kms:us-east-1:XXXXXXXXXX:key/xxxxxxxx-xxxx-xxxx-xxxx-xxxx' -- update your kms key ARN

DECLARE DBBackup CURSOR FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('rdsadmin', 'master', 'model', 'msdb', 'tempdb')
AND state_desc = 'ONLINE'
AND user_access_desc = 'MULTI_USER'
AND is_read_only = 0

OPEN DBBackup
FETCH NEXT FROM DBBackup INTO @DBName
WHILE (@@FETCH_STATUS = 0)

BEGIN
SET @BackupFileName = @DBName + '_' + REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(),20),'-',''),':',''),' ','') + '.bak'
SET @S3ARN = @S3ARN_Prefix + @BackupFileName
EXEC msdb.dbo.rds_backup_database
@source_db_name = @DBName,
@S3_arn_to_backup_to = @S3ARN,
@KMS_master_key_arn = @KMS_master_key_ARN,
@overwrite_S3_backup_file = 0
FETCH NEXT FROM DBBackup INTO @DBName
END

To track the status of the job, use this SQL statement:

exec msdb..rds_task_status @task_id= 5 -- 5 as an example of your task id

SQL Server : Performance degraded after upgrading the compatibility level from 2008 to 2014/2017/2019 (i.e. 100 to 120 Compatibility version).

In this article, we will discuss about the performance impact after change in compatibility level of your databases.

Please allow me to shed some light about the concept of Compatibility Level, as you know prior to SQL Server 2014, the database compatibility level of your user databases was not typically an important property that you had to be concerned with, at least from a performance perspective. Unlike the database file level (which gets automatically upgraded when you restore or attach a down-level database to an instance running a newer version of SQL Server, and which can never go back to the lower level), the database compatibility level can be changed to any supported level with a simple ALTER DATABASE SET COMPATIBILITY LEVEL = xxx command.

With SQL Server 2012 and older, the database compatibility level was mainly used to control whether new features introduced with a particular version of SQL Server were enabled or not and whether non-supported old features were disabled or not. The database compatibility level was also used as a method to maintain better backwards application compatibility with old versions of SQL Server. If you didn’t have time to do full regression testing with the newest compatibility level, you could just use the previous compatibility level until you could test and modify your applications if needed.

Now when you create a new user database in SQL Server, the database compatibility level will be set to the default compatibility level for that version of SQL Server. So for example, a new user database that is created in SQL Server 2017 will have a database compatibility level of 140. The exception to this is if you have changed the compatibility level of the model system database to a different supported database compatibility level, then a new user database will inherit its database compatibility level from the model database.

But Database Compatibility Level 120, this was when the “new” cardinality estimator (CE) was introduced. In many cases, most of your queries ran faster when using the new cardinality estimator, but it was fairly common to run into some queries that had major performance regressions with the new cardinality estimator. Using database compatibility level 120 means that you will be using the “new” CE unless you use an instance-wide trace flag or a query-level query hint to override it.

You can refer the below article from MS on the Differences between lower compatibility levels and level 120; https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-2017#differences-between-lower-compatibility-levels-and-level-120
Also there classic whitepaper wrote by Joe Sack on “Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator” that explains the background and behavior of this change back in April of 2014. ; https://docs.microsoft.com/en-us/previous-versions/dn673537(v=msdn.10)?redirectedfrom=MSDN

Microsoft’s recommended upgrade process is :
[+] Change the Database Compatibility Level and use the Query Store; https://docs.microsoft.com/en-us/sql/database-engine/install-windows/change-the-database-compatibility-mode-and-use-the-query-store?view=sql-server-2017
- Upgrade to the latest SQL Server version and keep the source (legacy) database compatibility level.
- Enable Query Store, and let it collect a baseline of your workload.
- Change the database compatibility level to the native level for the new version of SQL Server.
- Use Query Store to fix performance regressions by forcing the last known good plan.

Please refer the below articles on the same :
[+] https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-2017

Microsoft also introduce the new tool Query Tuning Assistant; Starting with SQL Server Management Studio v18, the new Query Tuning Assistant (QTA) feature will guide users through the recommended workflow to keep performance stability during upgrades to newer SQL Server versions, as documented in the section Keep performance stability during the upgrade to newer SQL Server of Query Store Usage Scenarios. However, QTA doesn't roll back to a previously known good plan as seen in the last step of the recommended workflow. Instead, QTA will track any regressions found in the Query Store Regressed Queries view, and iterate through possible permutations of applicable optimizer model variations so that a new better plan can be produced.

Please have a look into the below article for more details on The Query Tuning Assistant workflow; https://docs.microsoft.com/en-us/sql/relational-databases/performance/upgrade-dbcompat-using-qta?view=sql-server-2017#the-query-tuning-assistant-workflow

RDS SQL Server : Unable to restore and getting an error message : "There is not enough space on the disk to perform restore database operation."

If you are getting an error message as "There is not enough space on the disk to perform restore database operation."

-- This issue appears when rds restore starts, RDS makes an estimate about how much disk space will be required by the database that is going to be restored. If that estimate exceeds the amount of free space left on the disk, you will see this error.

Kindly note that as part of the restore process is trying to look for the data and log file size as was configured on the source and if the amount of space is not available on disk upfront its failing the restore process in the first place while trying perform the restore and also the SQL server restore process will not initiate the Storage Autoscaling.

For example: lets say the source database (On-prem\EC2\RDS sql server instance) is configured with 30 GB of data file and 30 GB of log file sizes and the used space inside the 30 GB data file is only 10 GB and used space inside the 30 GB log file is only 10 GB.The backup size of the database in this case would be only 20 GB(with compression this backup file size can further be reduced) out of actual 60 GB configured on the source database.During the restore of this 20 GB backup file on the RDS SQL server instance the process looks for the 60 GB of space on the underlying storage per the source data and log file configurations.In this case if the Target RDS database instance if it has 50 GB of underlying storage so restore process would fail as it requires 60 GB of storage space to proceed with the restore and it fails with the space issue.

Now I would suggest you to please check the actual database size on the source database using below script:
--Useful script to know the current size of database files and the free space.
USE [DBNAME] -- update your db name
GO
SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;

If log is using the most of the storage space then you can try to shrink the log file and then again the backup and try to restore again.

AWS : Relational Database Service (RDS) - Basics Part 4

 ==============================================================
How do you maintain my database ? How can I plan for it?

• Any maintenance that causes downtime (typically only a few times per year) will be scheduled in your maintenance window
• Operating system or Amazon RDS software patches are usually performed without restarting databases
• Database engine upgrades require downtime
    • Minor version upgrades — automatic or manually applied (Automatic not applicable in case of SQL Server, always manual)
    • Major version upgrades — manually applied
    • Version deprecations—three-to six-month notification before scheduled upgrades
• View upcoming maintenanceevents in your AWS Personal Health Dashboard

==============================================================
How am I charged for Amazon RDS ?

• Database instance (instance hours)
    • Combination of Region + instance type + database engine + license (optional)
• Database storage (GB-mo)
    • Can be either provisioned (Amazon EBS) or consumed (Amazon Aurora)
    • Provisioned IOPS (IOPS-Mo) for IO1 storage type
    • Database I/O requests (IOs) for Amazon Aurora and Amazon EBS magnetic-storage types
• Backup storage (GB-mo
    • Size of backups and snapshots stored in Amazon S3
    • No charge for backup storage up to 100% of total database storage
• Data transfer (GB-mo)
    • Uses AWS regional data-transfer pricing

==============================================================
How can I save money on my database?

• Amazon RDS Reserved Instances (RIs) provide a discount over on-demand prices
• Region, instance family, and engine of on-demand usage must match to apply benefit
• Amazon RDS RIs offer size flexibility for open-source and Oracle BYOL engines
• By default, RIs are shared between allaccounts in consolidated billing
• Use the RI utilization report to determinehow your RIs are being used
    • Support for RI coverage reportcoming soon

==============================================================
Can I stop my database when it’s not in use?

• Stop and start a running database instance from the console or AWS CLI
• Available for single-AZ DB instances
• While instance is stopped, you only pay for storage
• Backup retention window is maintained while stopped
• Instances are restarted after 7 days
    • Pending maintenance operations are applied
    • Instances can be stopped again if desired

==============================================================

AWS : Relational Database Service (RDS) - Basics Part 3

 ==============================================================
How do I secure my Amazon RDS database?

• Amazon RDS is designed to be secure by default
• Network isolation with Amazon Virtual Private Cloud (Amazon VPC)
• AWS Identity and Access Management (IAM)-based resource-level permission controls
• Encryption at rest using AWS KMS (all engines) or Oracle/Microsoft TDE
• Use SSL protection for data in transit

==============================================================
What does Amazon VPC provide?

• Places your instance in a private subnet, making it secure from public routes on the Internet
• Database instance IP firewall protection lets you securely control network configuration
• Turn off Public Accessibility in DB instance settings to restrict access outside Amazon VPC
• Use ClassicLink to network with non-VPC resources

==============================================================
How do I grant access to my database?

• Use IAM to control who can perform actions on RDS resources
• Do not use AWS root credentials to manage Amazon RDS resources—you should create an IAM user for everyone, including yourself
• Can use AWS Multi-Factor Authentication (MFA) to provide extra level of protection

==============================================================
How do I encrypt my database?

- Use AWS KMS-based encryption in the AWS console
- No performance penalty for encrypting data
- Centralized access and audit of key activity
- Best practices
    • Encryption cannot be removed from DB instances
    • If source is encrypted, Read Replicas must be encrypted
    • Add encryption to an unencrypted DB instance by encrypting a snapshot copy

==============================================================
How do I monitor my Amazon RDS database ?

- Amazon CloudWatch metrics
    • CPU/Storage/Memory•Swap usage
    • I/O (read and write)
    • Latency (read and write)
    • Throughput (read and write)
    • Replica lag
- Amazon CloudWatch Alarms
    • Similar to on-premises monitoring tools
- Enhanced monitoring for Amazon RDS
    • Access to over 50 CPU, memory, file system, and disk I/O metrics
    • Low as 1-second intervals
-Integration with third-party monitoring tools

==============================================================
How do I improve database performance?

• Introducing Amazon RDS Performance Insights
• Measures DB Load: Average Active Sessions (AAS)
• Identifies database bottlenecks (Top SQL):
    • Easy
    • Powerful
• Identifies source of bottlenecks
• Enables problem discovery
• Adjustable time frame
    • Hour, day, week, and longer
• Coming soon for Amazon EBS-based Amazon RDS engines

==============================================================
Can I know when service events happen?

• Amazon RDS uses Amazon SNS to receive notification when an event occurs
• Notifications can be in any form supported by Amazon SNS (email, a text message, or a call to an HTTP endpoint)
• Six different source types (DB instance, DB parameter group, DB security group, DB snapshot, DB cluster, DB cluster snapshot)
• 17 different event categories (availability, backup, deletion, configuration change, etc.)

==============================================================

AWS : Relational Database Service (RDS) - Basics Part 2

 ==============================================================
How do I ensure database high availability?

• Multi-AZ provides enterprise-grade fault-tolerance solution for production databases
    • Automatic failover
    • Synchronous replication
    • Inexpensive and enabled with one click

==============================================================
What happens during a Multi-AZ failover? How long does it take?

• Each host manages set of Amazon EBS volumes with a full copy of the data
• Instances are monitored by an external observer to maintain consensus over quorum
• Failover initiated by automation or through the Amazon RDS API
• Redirection to the new primary instance is provided through DNS

==============================================================
Why would I use Read Replicas?

• Relieve pressure on your source database with additional read capacity
• Bring data close to your applications in different regions
• Promote a Read Replica to a master for faster recovery in the event of disaster
• Upgrade a Read Replica to a new engine version•Supported for MySQL, MariaDB, and PostgreSQL

==============================================================
When should I use Multi-AZ ?

 • Synchronous replication—highly durable
 • Only primary instance is active at any point in time
 • Backups can be taken from secondary
 • Always in two Availability Zones within a Region•Database engine version upgrades happen on primary
 • Automatic failover when a problem is detected

==============================================================
When should I use Read Replicas ?

 • Asynchronous replication—highly scalable
 • All replicas are active and can be used for read scaling
 • No backups configured by default•Can be within an Availability Zone, cross-AZ, or cross-region
 • Database engine version upgrades independently from source instance
 • Can be manually promoted to a standalone databaseRead ReplicasMulti-AZ

==============================================================
How does Amazon RDS manage backups?

• Two options –automated backups and manual snapshots
• Amazon RDS backups leverage Amazon EBS snapshots stored in Amazon S3
• Transaction logs are stored every 5 minutes in Amazon S3 to support point-in-time recovery (PITR)
• No performance penalty for backups
• Snapshots can be copied across regions or shared with other accounts

==============================================================
When should I use automated backups?

• Specify backup retention window per instance (7-day default)
• Kept until outside of window (35-day maximum) or instance is deleted
• Supports PITR
• Good for disaster recovery

==============================================================
When should I use snapshots?

• Manually created through AWS console, AWS CLI, or Amazon RDS API
• Kept until you delete them
• Restores to saved snapshot
• Use for checkpoint before making large changes, non-production/test environments, final copy before deleting a databaseManual snapshots

==============================================================
How do I restore a backup? Why does it take so long?

- Restoring creates an entirely new database instance
    • Define the instance configuration just like a new instance
    • Will get the default parameter, security, and option groups

- New volumes are hydrated from Amazon S3
    • While the volume is usable immediately, full performance requires the volume to warm up until fully instantiated
    • Migrate to a DB instance class with high I/O capacity
    • Maximize I/O during restore process

==============================================================

AWS : Relational Database Service (RDS) - Basics Part 1

==============================================================
What is Amazon RDS?

• Managed relational database service in the AWS cloud
• Multi-engine support: Amazon Aurora, MySQL, MariaDB, PostgreSQL, Oracle, SQL Server
• Automated provisioning, patching, scaling, replicas, backup/restore
• Easily scales to handle growth•High availability with Multi-AZ and Amazon Aurora

==============================================================

Why use Amazon RDS?

• Lower TCO because RDS manage “the muck”
    • Get more leverage from your teams
    • Focus on the things that differentiate you
• Built-in high availability and cross-region replication across multiple data centers
• Even a small startup can leverage multiple data centers to design highly available apps with over 99.95% availability

==============================================================
Which RDS engine should I use?

Amazon EBS-based Storage :
- Commercial : SQL Server and Oracle
- Open Source : MySQL , PostgreSQL and MariaDB
Aurora Storage System :
- AWS Cloud Native : MySQL CompatiblePostgreSQL Compatible

==============================================================
Which instance type should I choose?

- T2 Family
    • Burstable instances
    • 1 vCPU/1 GB RAM > 8  vCPU 32 GB RAM
    • Moderate networking performance
    • Good for smaller or variable workloads
    • Monitor CPU credit metrics in Amazon CloudWatch
    • T2.micro is eligible for free tier

- M3/M4 Family
    • General-purpose instances
    • 2 vCPU/8 GiB RAM > 64 vCPU 256 GiB RAM
    • High-performance networking
    • Good for running CPU intensive workloads (e.g., WordPress)

- R3/R4 Family
    • Memory-optimized instances
    • 2 vCPU/16 GiB RAM > 64 vCPU 488 GiB RAM
    • High-performance networking
    • Good for query-intensive workloads or high connection counts
==============================================================
Configuring DB Instance Class Processor ?

Modify processor features to optimize DB instance for specific workloads:
• Number of CPU Cores –Optimize licensing costs for instance with sufficient RAM for memory-intensive workloads, but fewer CPU cores
• Threads per Core –Disable Hyper-Threading for, e.g. HPC workloads

Changes can be applied when you:
• Create a DB instance
• Modify a DB instance
• Restore a DB instance from a snapshot
• Restore a DB instance to a point in time
==============================================================
Which AWS EBS storage type should I choose?

- General purpose (GP2)
    • SSD storage
    • Maximum of 16 TB
    • Leverages Amazon EBS Elastic Volumes
    • IOPS determined by volume size
    • Minimum of 100 IOPS (below 33.33GiB)
    • Bursts to 3,000 IOPS (applicable below 1.3 TB)
    • Baseline of 16,000 IOPS (at 3.3 TB and above)
    • Affordable performance

- Provisioned IOPS (IO1)
    • SSD storage•Maximum of 16 TB
    • Leverages Amazon EBS Elastic Volumes
    • Maximum of 40K IOPS (32K on SQL Server)
    • Delivers within 10% of the IOPS performance 99.9% of the time
    • High performance and consistency

- Magnetic
    • Magnetic storage
    • Maximum of 1 TB
    • Supported for legacy databases

- General purpose (GP3), is coming soon.
==============================================================
How do I decide between GP2 and IO1 AWS EBS Storage Type? Why am I not seeing less IOPS or only 30 K IOPS?

• GP2 is a great choice, but be aware of burst credits on volumes < 1 TB
    • Hitting credit-depletion results in IOPS drop—latency and queue depth metrics will spike until credits are replenished
    • Monitor BurstBalance to see percent of burst-bucket I/O credits available
    • Monitor read/write IOPS to see if average IOPS is greater than the baseline

==============================================================
How do I scale my database instance? Will there be downtime ?

• Scale compute/memory vertically up or down
    • Handle higher load to grow over time
    • Lower usage to control costs
    • New host is attached to existing storage with minimal downtime

• Scale up Amazon EBS storage (now up to 16 TB)
    • Amazon EBS engines now support Elastic Volumes for fast scaling (now including SQL Server)
    • No downtime for storage scaling
    • Initial scaling operation may take longer, because storage is reconfigured on older instances
    • Can re-provision IOPS on the fly

==============================================================

SQL AlwaysON : TSQL Script to automatically add a new step to TestForPrimary

TSQL Script to automatically add a new step to TestForPrimary for SQL AlwaysOn Solution:

USE [DBA]
GO
SET NOCOUNT ON
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--Job

DECLARE @jobID nvarchar(40),@stepID nvarchar(40),@stepID_nxt nvarchar(40),@procName varchar (255),
@jobName varchar (255), @command_job varchar(8000),@command_step varchar(8000),
@command_end varchar(8000),@command_sched varchar(8000),
@CommandString NVARCHAR(1000),@command_main nvarchar(max)

DECLARE @job_name as varchar(255),@owner_login_name as varchar(255),@description as varchar(255),
@category_name as varchar(255),@enabled as nvarchar(4)

DECLARE @notify_level_email as nvarchar(4),@notify_level_page as nvarchar(4),
@notify_level_netsend as nvarchar(4)

DECLARE @notify_level_eventlog as nvarchar(4),@delete_level as nvarchar(4),
@start_step_id nvarchar(4)

--Job Steps

DECLARE @step_name varchar(255),@command varchar(8000),@database_name varchar(255),
@database_user_name varchar(255), @subsystem nvarchar(40)

DECLARE @cmdexec_success_code nvarchar(2),@flags nvarchar(2),@retry_attempts nvarchar(2),
@retry_interval nvarchar(2),@output_file_name varchar (255)

DECLARE @on_success_step_id nvarchar(3),@on_success_action nvarchar(2),
@on_fail_step_id nvarchar(2),@on_fail_action nvarchar(2)

     
SET @CommandString = 'use master
GO
Declare @AGName varchar(10);
SELECT TOP(1) @AGName= name FROM sys.availability_groups;

if (SELECT ars.role_desc from sys.dm_hadr_availability_replica_states
ars inner join sys.availability_groups ag 
on ars.group_id = ag.group_id where ag.name = @AGName and ars.is_local = 1) = ''PRIMARY''
BEGIN
PRINT ''This is Primary Replica - execute job''
END

ELSE

BEGIN
-- we''re not in the Primary - exit gracefully: Deliberately cause a Failure
SELECT 1/0
PRINT ''This is Secondary replica - exiting with success'';
END'


if EXISTS(select * from DBA.INFORMATION_SCHEMA.TABLES where table_name='server_agent_jobs')

BEGIN

DROP TABLE DBA..server_agent_jobs

End

--ignore multi-server jobs at this time

select sj.job_id, name, '0' as executed,category_id,'1' as spcreated,
'1999-12-31 00:0000000' as createddate,
('EXECUTE [dbo].[sp_create_server_agent_job_' + name +']') as excmd,
('sp_create_server_agent_job_' + name) as spname into DBA..server_agent_jobs 
from msdb..sysjobs sj inner join msdb..sysjobservers ss on ss.job_id = sj.job_id 
and ss.server_id = '0' order by sj.job_id

WHILE EXISTS (select job_id from DBA..server_agent_jobs where executed = '0')

BEGIN

--get working job_id and stored proc name

SELECT top 1 @jobID = job_id, @procName = 'sp_create_server_agent_job_' + name, @jobName = name
FROM DBA..server_agent_jobs WHERE executed = '0' order by job_id


--get job info from sysjobs

select @job_name = a.name,@owner_login_name = b.name,@description = a.description,
@category_name = c.name,@enabled = a.enabled,
@notify_level_email = a.notify_level_email,@notify_level_page = a.notify_level_page,
@notify_level_netsend = a.notify_level_netsend,@notify_level_eventlog = a.notify_level_eventlog,
@delete_level = a.delete_level, @start_step_id = a.start_step_id
FROM msdb..sysjobs a, master..syslogins b, msdb..syscategories c
WHERE a.owner_sid = b.sid and a.category_id = c.category_id and job_id = @jobID

--get job info from sysjobsteps

if EXISTS(select * from DBA.INFORMATION_SCHEMA.TABLES where table_name='server_agent_job_steps')

BEGIN
DROP TABLE DBA..server_agent_job_steps
End

select *, '0' as executed into DBA..server_agent_job_steps
from msdb..sysjobsteps sjs where job_id = @jobID order by sjs.step_id

if EXISTS(select * from DBA.INFORMATION_SCHEMA.TABLES
where table_name='server_agent_job_schedules')

BEGIN
DROP TABLE DBA..server_agent_job_schedules
End

select *, '0' as executed into DBA..server_agent_job_schedules from msdb..sysjobschedules sjs
where job_id = @jobID order by sjs.schedule_id

--drop proc if it exists before it's re-created

if EXISTS(select * from DBA.INFORMATION_SCHEMA.ROUTINES where routine_name = @procName)
BEGIN
exec('DROP PROCEDURE [' + @procName + ']')
End

SET CONCAT_NULL_YIELDS_NULL OFF

select @command_job = 'CREATE Procedure [dbo].[' + @procName + '](
@operator varchar(255)=NULL
)

AS

BEGIN
SET NOCOUNT OFF
DECLARE @ReturnCode nvarchar (40)--, @jobID nvarchar (40)

Begin Transaction

--delete job if it already exists (by job name -> @jobName)

if EXISTS(select * from msdb..sysjobs where name = ''' + @jobName + ''')
begin
Print ''Job Updated''
end

--add job steps

'
WHILE EXISTS (select * from DBA..server_agent_job_steps where executed = '0')

BEGIN
select top 1 @stepID = step_id from DBA..server_agent_job_steps
where executed = '0' order by step_id desc

SELECT @step_name = a.step_name,@command = a.command, @database_name = a.database_name,
@database_user_name = a.database_user_name,
@subsystem = a.subsystem, @cmdexec_success_code = a.cmdexec_success_code, @flags = a.flags,
@retry_attempts = a.retry_attempts,
@retry_interval = a.retry_interval, @output_file_name = a.output_file_name,
@on_success_step_id = a.on_success_step_id,
@on_success_action = a.on_success_action, @on_fail_step_id = a.on_fail_step_id,
@on_fail_action = a.on_fail_action
FROM DBA..server_agent_job_steps a where a.step_id = @stepID

select @command_job = @command_job +'
EXECUTE msdb.dbo.sp_delete_jobstep
@job_id = ''' + @JobID + ''',
@step_id = '+ @stepID +'

'
update DBA..server_agent_job_steps set executed = '1' where step_id = @stepID
END

update DBA..server_agent_job_steps set executed = '0'

SELECT @command_step = 'EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep 
@job_id=''' + @JobID + ''',
   @step_name =''TestForPrimary'',
                  @step_id=1,
                  @cmdexec_success_code=0,
                  @on_success_action=3,
                  @on_success_step_id=0,
                  @on_fail_action=1,
                  @on_fail_step_id=0,
                  @retry_attempts=0,
                  @retry_interval=0,
                  @os_run_priority=0,@subsystem=''TSQL'',
                  @command = ''' + REPLACE (@CommandString,'''','''''') + ''',
                  @database_name=''master'',
                  @flags=0

'

WHILE EXISTS (select * from DBA..server_agent_job_steps where executed = '0')

BEGIN
select top 1 @stepID = step_id from DBA..server_agent_job_steps
where executed = '0' order by step_id

SELECT @step_name = a.step_name,@command = a.command,
@database_name = a.database_name,@database_user_name = a.database_user_name,
@subsystem = a.subsystem, @cmdexec_success_code = a.cmdexec_success_code,
@flags = a.flags, @retry_attempts = a.retry_attempts,
@retry_interval = a.retry_interval, @output_file_name = a.output_file_name,
@on_success_step_id = a.on_success_step_id,
@on_success_action = a.on_success_action, @on_fail_step_id = a.on_fail_step_id,
@on_fail_action = a.on_fail_action
FROM DBA..server_agent_job_steps a where a.step_id = @stepID

SET @stepID_nxt=@stepID+1

select @command_step = @command_step + 'EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep

@job_id = ''' + @JobID + ''',
@step_id = '''+ @stepID_nxt +''',
@step_name = ''' + @step_name + ''',
@command = ''' + REPLACE (@command,'''','''''') + ''',
@database_name = ''' + @database_name + ''',
@server = ''' + '' + ''',
@database_user_name = ''' + @database_user_name + ''',
@subsystem = ''' + @subsystem + ''',
@cmdexec_success_code = ''' + @cmdexec_success_code + ''',
@flags = ''' + @flags + ''',
@retry_attempts = ''' + @retry_attempts + ''',
@retry_interval = ''' + @retry_interval + ''',
@output_file_name = ''' + @output_file_name + ''',
@on_success_step_id = ''' + @on_success_step_id + ''',
@on_success_action = ''' + @on_success_action + ''',
@on_fail_step_id = ''' + @on_fail_step_id + ''',
@on_fail_action = ''' + @on_fail_action + '''

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

'

update DBA..server_agent_job_steps set executed = '1' where step_id = @stepID
END

--update start step id from sysjobs

select @command_step = @command_step + 'EXECUTE @ReturnCode = msdb.dbo.sp_update_job
@job_id = ''' + @JobID + ''',
@start_step_id = ''' + @start_step_id + '''

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

'

select @command_end = '

COMMIT TRANSACTION
GOTO THE_END

QuitWithRollback:
print ''job failed''

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

THE_END:
END'

--print (@command_job + @command_step + @command_sched + @command_end)

SET CONCAT_NULL_YIELDS_NULL ON

execute (@command_job + @command_step + @command_sched + @command_end)

update DBA..server_agent_jobs set executed = '1',createddate=getdate()
where DBA..server_agent_jobs.job_id = @jobID

select @command_step = ''

END

update DBA..server_agent_jobs set spcreated = 0,excmd='--NA',spname='--NA'
where spname NOT IN (SELECT SPECIFIC_Name FROM DBA.INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE' and SPECIFIC_NAME like 'sp_create_server_agent_job_%')

PowerShell to Send Mail in HTML format using SMTP

PowerShell Script to Send mail in html format using smtp Server:

PS Script to Send mail with attachment(optional):
Information required:
1. Create ".html page" like in script using "content.html"
2. Make a note of file path, if needs to be attached with an email.
3. smtp server details.
4. Save the below script somewhere with ".ps1" extension, That's it!!

############################################

###########Define Variables########

$fromaddress = "donotreply@labtest.com"
$toaddress = "A@labtest.com"
$bccaddress = "A@labtest.com"
$CCaddress = "A@labtest.com"
$Subject = "ACtion Required"
$body = get-content C:\dba_path\content.html
$attachment = "C:\dba_path\test.txt"
$smtpserver = "smtp.lab.com"

####################################

$message = new-object System.Net.Mail.MailMessage
$message.From = $fromaddress
$message.To.Add($toaddress)
$message.CC.Add($CCaddress)
$message.Bcc.Add($bccaddress)
$message.IsBodyHtml = $True
$message.Subject = $Subject
$attach = new-object Net.Mail.Attachment($attachment)
$message.Attachments.Add($attach)
$message.body = $body
$smtp = new-object Net.Mail.SmtpClient($smtpserver)
$smtp.Send($message)

###############################################

MySQL : Basic usefull Command Part 2

11. How to reload/refresh MySQL Privileges?

# mysqladmin -u root -p reload;
# mysqladmin -u root -p refresh

12. How to shutdown MySQL server Safely?

# mysqladmin -u root -p shutdown

# /etc/init.d/mysqld stop
# /etc/init.d/mysqld start

13. Some useful MySQL Flush commands

# mysqladmin -u root -p flush-hosts
# mysqladmin -u root -p flush-tables
# mysqladmin -u root -p flush-threads
# mysqladmin -u root -p flush-logs
# mysqladmin -u root -p flush-privileges
# mysqladmin -u root -p flush-status

1. flush-hosts: Flush all host information from host cache.
2. flush-tables: Flush all tables.
3. flush-threads: Flush all threads cache.
4. flush-logs: Flush all information logs.
5. flush-privileges: Reload the grant tables (same as reload).
6. flush-status: Clear status variables
   
   

14. How to kill Sleeping MySQL Client Process?

 # mysqladmin -u root -p processlist

Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 5  | root | localhost |    | Sleep   | 14   |       |					 |
| 8  | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

# mysqladmin -u root -p kill 5

Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 12 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

To Kill multiple:

# mysqladmin -u root -p kill 5,10

15. How to run multiple mysqladmin commands together?

# mysqladmin  -u root -p processlist status version

Enter password:
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 8  | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
Uptime: 3801  Threads: 1  Questions: 15  Slow queries: 0  
Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.003
mysqladmin  Ver 8.42 Distrib 5.5.28, for Linux on i686
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          5.5.28
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 1 hour 3 min 21 sec

16. How to Connect remote mysql server ?

# mysqladmin  -h 19x.1x.x5.22x -u root -p

17. How to execute command on remote MySQL server?

# mysqladmin  -h 19x.1x.x5.22x -u root -p status

18. How to start/stop MySQL replication on a slave server?

 # mysqladmin  -u root -p start-slave

# mysqladmin  -u root -p stop-slave

19. How to store MySQL server Debug Information to logs?

# mysqladmin  -u root -p debug

20. How to view mysqladmin options and usage ?

# mysqladmin --help

MySQL : Basic usefull Command Part 1

1. How to set MySQL Root password?

# mysqladmin -u root password YOURNEWPASSWORD

2. How to Change MySQL Root password?

 mysqladmin -u root -p123456 password 'xyz123'

3. How to check MySQL Server is running?

# mysqladmin -u root -p ping

Enter password:
mysqld is alive

4. How to Check which MySQL version I am running?

# mysqladmin -u root -p version

Enter password:
mysqladmin  Ver 8.42 Distrib 5.5.28, for Linux on i686
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          5.5.28
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 7 days 14 min 45 sec

5. How to Find out current Status of MySQL server?

# mysqladmin -u root -ptmppassword status

Enter password:
Uptime: 606704  Threads: 2  Questions: 36003  Slow queries: 0
Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.059

6. How to check status of all MySQL Server Variable’s and value’s?

# mysqladmin -u root -p extended-status

Enter password:
+------------------------------------------+-------------+
| Variable_name                            | Value       |
+------------------------------------------+-------------+
| Aborted_clients                          | 3           |
| Aborted_connects                         | 3           |
| Binlog_cache_disk_use                    | 0           |
| Binlog_cache_use                         | 0           |
| Binlog_stmt_cache_disk_use               | 0           |
| Binlog_stmt_cache_use                    | 0           |
| Bytes_received                           | 6400357     |
| Bytes_sent                               | 2610105     |
| Com_admin_commands                       | 3           |
| Com_assign_to_keycache                   | 0           |
| Com_alter_db                             | 0           |
| Com_alter_db_upgrade                     | 0           |
| Com_alter_event                          | 0           |
| Com_alter_function                       | 0           |
| Com_alter_procedure                      | 0           |
| Com_alter_server                         | 0           |
| Com_alter_table                          | 0           |
| Com_alter_tablespace                     | 0           |
+------------------------------------------+-------------+

7. How to see all MySQL server Variables and Values?

To see all the running variables and values of MySQL server, use the command as follows.

# mysqladmin  -u root -p variables

Enter password:
+---------------------------------------------------+------------------------------+
| Variable_name                                     | Value                        |
+---------------------------------------------------+------------------------------+
| auto_increment_increment                          | 1                            |
| auto_increment_offset                             | 1                            |
| autocommit                                        | ON                           |
| automatic_sp_privileges                           | ON                           |
| back_log                                          | 50                           |
| basedir                                           | /usr                         |
| big_tables                                        | OFF                          |
| binlog_cache_size                                 | 32768                        |
| binlog_direct_non_transactional_updates           | OFF                          |
| binlog_format                                     | STATEMENT                    |
| binlog_stmt_cache_size                            | 32768                        |
| bulk_insert_buffer_size                           | 8388608                      |
| character_set_client                              | latin1                       |
| character_set_connection                          | latin1                       |
| character_set_database                            | latin1                       |
| character_set_filesystem                          | binary                       |
| character_set_results                             | latin1                       |
| character_set_server                              | latin1                       |
| character_set_system                              | utf8                         |
| character_sets_dir                                | /usr/share/mysql/charsets/   |
| collation_connection                              | latin1_swedish_ci            |
+---------------------------------------------------+------------------------------+

8. How to check all the running Process of MySQL server?

# mysqladmin -u root -p processlist

Enter password:
+-------+---------+-----------------+---------+---------+------+
| Id    | User    | Host            | db      | Command | Time |
+-------+---------+-----------------+---------+---------+------+
| 18001 | rsyslog | localhost:38307 | rsyslog | Sleep   | 5590 |
| 18020 | root    | localhost       |         | Query   | 0    |
+-------+---------+-----------------+---------+---------+------+

9. How to create a Database in MySQL server?

# mysqladmin -u root -p create mydatabasename

Enter password:

# mysql -u root -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18027
Server version: 5.5.28 MySQL Community Server (GPL) by Remi

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydatabasename       |
| mysql              |
| test               |
+--------------------+
8 rows in set (0.01 sec)

mysql>

10. How to drop a Database in MySQL server?

# mysqladmin -u root -p drop databasename

Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'databasename' database [y/N] y
Database "databasename" dropped

SQL Server Latch Vs Locks

SQL Server Latch VS Lock

Latch
Latches are very lightweight, short-term synchronization objects protecting actions that need not be locked for the life of a transaction. They are primarily used to protect a row when read for a connection.

Lock
Locks in SQL Server protects the tables or data pages currently used by active transactions by locking them. Locking is a concurrency control mechanism: it ensures the consistency of data across transactions. It is needed in a multi-user environment, since several users may be working with the same data at the same time.

Why do we need Latches/Locks for Database?
Latches were first introduced in SQL Server 7.0, when Microsoft first introduced row-level locking. For row-level locking it was very important to introduce a concept like latching, because otherwise it would give rise to phenomena like Lost Updates in memory.

A page in SQL Server is 8KB and can store multiple rows. To increase concurrency and performance, buffer latches are held only for the duration of the physical operation on the page, unlike locks which are held for the duration of the logical transaction. Latches are internal to the SQL engine and are used to provide memory consistency, whereas locks are used by SQL Server to provide logical transactional consistency.

When the relational engine is processing a query, each time a row is needed from a base table or index, the relational engine uses the OLE DB API to request that the storage engine return the row. While the storage engine is actively transferring the row to the relational engine, the storage engine must ensure that no other task modifies either the contents of the row or certain page structures such as the page offset table entry locating the row being read. The storage engine does this by acquiring a latch, transferring the row in memory to the relational engine, and then releasing the latch.

SQL Server Performance Monitor has a Latches object that indicates how many times latches could not be granted immediately and the amount of time threads spent waiting for latches to be granted.

Latches are often confused with locks, as their purposes are similar but not the same. A latch can be defined as an object that ensures data integrity on other objects in SQL Server memory, particularly pages. They are a logical construct that ensures controlled access to a resource and isolationism when required for pages in use. In contrast to locks, latches are an internal SQL Server mechanism that isn't exposed outside the SQLOS.

While locks protect data during transactions, another process, latching, controls access to physical pages. Latches are very lightweight, short-term synchronization objects protecting actions that do not need to be locked for the life of a transaction. When the engine scans a page, it latches the page, reads the row, gives it back to the relational engine, and then unlatches the page again so another process can reach the same data. Through a process called lazy latching, the storage engine optimizes access to the data pages by releasing latches only when a page is also requested by another ongoing process. If no ongoing process requests the same data page, a single latch remains valid for the entire operation on that page.

Latching
SQL Server uses latches to provide data synchronization. A latch is a user-mode reader-writer lock implemented by SQL Server. Each data page in memory has a buffer (BUF) tracking structure. The BUF structure contains status information (Dirty, On LRU, In I/O) as well as a latch structure.

Locking maintains the appropriate lock activity; latching controls physical access. For example, it is possible for a lock to be held on a page that is not in memory. The latch is only appropriate when the data page is in memory (associated with a BUF).

The following list describes the different types of latches:

• I/O latches:
I/O Latches are used by SQL Server when outstanding I/O operations against pages in the Buffer Pool are done – when you read and write from/to your storage subsystem. For these I/O latches SQL Server reports a wait type that starts with PAGEIOLATCH_.
You can see the waiting times introduced with these types of latches in the DMV sys.dm_os_wait_stats.

• Non-buffer (Non-BUF) latch:
The non-buffer latches provide synchronization services to in-memory data structures or provide re-entrance protection for concurrency-sensitive code lines. These latches can be used for a variety of things, but they are not used to synchronize access to buffer pages.
- SQL Server also reports these latches in the DMV sys.dm_os_wait_stats with wait types starting with LATCH_.

• Buffer (BUF) latch:
The buffer latches are used to synchronize access to BUF structures and their associated database pages. The typical buffer latching occurs during operations that require serialization on a buffer page, (during a page split or during the allocation of a new page, for example). These latches are not held for the duration of a transaction.
- SQL Server also reports the waits introduced by these latches with wait types starting with PAGELATCH_*. These wait types are again reported to you through the DMV sys.dm_os_wait_stats.

SQL Server Cluster Network Name Resource ‘SQL Network Name’ Failed Issue

Issue
Today will discuss about the issue that I’ve encountered during performing an installation of a SQL Server failover cluster is “The cluster resource ‘SQL Server (MSSQLSERVER)’ could not be brought online due to an error bringing the dependency resource ‘SQL Network Name (MSSQL2012)’ online.” Upon checking the cluster events in the Failover Cluster Manager, you will find the below error.

Cluster network name resource 'SQL Network Name (MSSQL2012)' 
failed to create its associated computer object in domain 'ENTERPRISE.ORG' for the following reason: 
Resource online.
The associated error code is: -1073741790
Please work with your domain administrator to ensure that:
- The cluster identity 'WIN2012$' can create computer objects. 
By default, all computer objects are created in the 'Computers' container; consult the domain 
administrator if this location has been changed.
- The quota for computer objects has not been reached.
- If there is an existing computer object, 
verify the Cluster Identity ' WIN2012$' has 'Full Control' permission to that computer object 
using the Active Directory Users and Computers tool.

Lets first understand, what is Cluster Name Object (CNO)?
In a Windows Server Failover Cluster, a cluster name object (CNO) is an Active Directory (AD) account for a failover cluster.

A CNO is automatically created during cluster Setup. When the administrator creates a failover cluster and configures clustered services or applications, the "Create Cluster Wizard" creates all the Active Directory computer accounts the failover cluster requires and gives each account specific permissions. The wizard also creates a computer account for the failover cluster itself; this account is called the cluster name object.

The CNO is important because other accounts are created through it. If the CNO is deleted or permissions for the account are changed, other computer accounts required by the cluster can't be created until the CNO and correct permissions are restored.

Beginning with Windows Server 2012, both the Create Cluster Wizard and the PowerShell cmdlet New-Cluster allow administrators to decide which organizational unit (OU) should contain the CNO.

There are basically two solution of this problem:

1. One resolution is a preventative action that can be done prior to beginning the installation of the SQL Server Failover Cluster, and
2. Second resolution is after the issue experienced during installation to be able to continue.
Both resolutions require access and permissions to AD.

Resolution
1. The resolution that will prevent this issue on future installations is to “pre-stage” the VCO.

- Log in as a user with permissions to create computer objects in the domain.
- Under Active Directory Users and Computers, create a “New Computer” object within the desired AD Container for the VCO (this will be your SQL Server Network Name).
- Once the object has been created, you can then add the CNO (this will be your WSFC Name) to the security of the VCO with “Full Control” over the VCO.

2. To achieve the resolution that will be reactive for your current errors, you need to grant the proper permissions to the CNO.

- Log in as a user with administrative permissions in the domain.
- Under Active Directory Users and Computers, grant the CNO (this will be your WSFC Name) “Create Computer Objects” permissions.

After doing this, you can retry your previously failed installation, and it should be successful. To verify if this issue was corrected, you can navigate within the SQL Server (MSSQLSERVER) Cluster group and attempt to bring the Server Name resource online.
If the resource is able to be brought online successfully and thats it!!

SQL Server Agent Missing Issue in Windows Failover Cluster

Issue
Today I'm going to share my experience which I've faced last week while installing SQL Server 2012 on a Windows Server 2008 R2 Failover Cluster. All SQL Server component got installed but it shows failed in the last. On further investigation, that happened due to CNO permission Issue. SQL Server cluster name was not created within AD, and Windows failover cluster name doesn’t possess the required permissions to create the object.

Here is the error:

The cluster resource ‘SQL Server (ClusterName)’ could not be brought online
 due to an error bringing the dependency resource ‘SQL Network Name(ClusterName)’ online.
 Refer to the Cluster Events in the Failover Cluster Manager for more information.

Once granted the proper permission then SQL Server cluster resource group was successfully brought online. Then noticed that the SQL Server Agent was not listed as a resource type under the Other Resources section of the cluster resource group.

Now here is the actual blog starts,
How do I manually add the SQL Server Agent to the cluster resource group?

You will also not be able to see the SQL Server Agent on the Other Resources section of the SQL Server cluster resource group means that it has not been created successfully. You can verify this by trying to add a new resource in the clustered resource group, that will not be listed in it.

Resolution:
Manually add the SQL Server Agent resource type to the SQL Server cluster resource group
Step 1 : Create the SQL Server Agent resource type

Using cmd prompt execute below command:

cluster.exe restype "SQL Server Agent" /create /DLL:SQAGTRES.DLL

Step 2 : Add the SQL Server Agent resource to the SQL Server Cluster Resource Group.

Using the Failover Cluster Manager, right-click on the SQL Server cluster resource group
select Add a resource -> More resources ... -> A - Add SQL Server Agent

Step 3 : Set the private properties of the SQL Server Agent resource.

We need to assign the VirtualServerName and InstanceName properties of the SQL Server Agent resource to match those of the SQL Server resource.

Using the Failover Cluster Manager, double-click the SQL Server Agent resource to open up the Properties window. Click on the Properties tab to display the VirtualServerName and InstanceName properties. Enter the appropriate values for the properties and click OK.

Step 4 : Add the SQL Server resource as a dependency for the SQL Server Agent resource you just created. Then add the SQL Server service as a dependency to the SQL Server Agent service as in a stand-alone instance.
Using the Failover Cluster Manager, click on the Dependencies tab of the SQL Server Agent Properties dialog box and select SQL Server under the Resource drop-down list. Click OK.

Step 5 : Modifying SQL Server registry keys

Having an incomplete or corrupted SQL Server installation also means that there are registry keys that have not been properly written or updated. It is important to backup your registry prior to performing these tasks.
5.1. Open the Registry Editor and navigate to the following registry hives.

For default instance:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\ConfigurationState

For a named Instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft Microsoft SQL Server\MSSQL10_50.INSTANCENAME\ConfigurationState

5.2. Check the values of all the registry keys. If the value is greater than 1, it means that there was a failure either during the installation or configuration phase while running the setup process. In my environment, all of the registry keys have a value of 2.

5.3. Change all of the registry key values to 1.

Step 6 : Run a Repair of the SQL Server 2008 R2 installation

After all of the ground work has been done, you can now perform a repair of the SQL Server instance. To do this, run the setup.exe from the SQL Server 2012 installation media and click the Maintenance link on the left-hand side. You can then click the Repair link to run the repair process.

Restart SQL Server Services and Thats it!!